﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using WHW_NetUtility;
using System.Windows.Forms;
using System.Data;

namespace MainFrame
{
    class sqliteHelper: DB_base
    {
        private SQLiteConnection conn;
        private SQLiteCommand comm;
        private SQLiteDataReader dataReader;
        private string dbName;
        private string password;

        public string m_dbName
        {
            get { return dbName; }
            set { dbName = value; }
        }
        public string m_password
        {
            get { return password; }
            set { password = value; }
        }
        public sqliteHelper(){}
        /// <summary>
        /// 初始化/创建数据库
        /// </summary>
        /// <param name="dbName_s">数据库名称/路径</param>
        /// <param name="password_s">数据库密码，可以为空</param>
        public sqliteHelper(string dbName_s,string password_s)
        {
            m_dbName = dbName_s;
            m_password = password_s;

            try
            {
                if (!string.IsNullOrEmpty(dbName))
                {
                    conn = new SQLiteConnection("Data Source=" + dbName);
                    conn.Open();
                }
                else
                {
                    MessageBox.Show("数据库路径名称不能为空！","出错",MessageBoxButtons.OK,MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper", "数据库创建出错，数据库名称为空");
                    return;
                }
                if (!string.IsNullOrEmpty(password))
                {
                    conn.ChangePassword(password);
                }
                comm = new SQLiteCommand();
                comm.Connection = conn;
                //conn.Close();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("数据库初始化失败"+Environment.NewLine+ex.Message, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.CreateDB", ex);
            }
        }
        /// <summary>
        /// 创建数据库或打开数据库，如果已创建，则打开，如果没有，则重新创建
        /// </summary>
        /// <returns>成功打开返回true，否则返回false</returns>
        public override bool OpenDB()
        {
            try
            {
                if (!string.IsNullOrEmpty(dbName))
                {
                    conn = new SQLiteConnection("Data Source=" + dbName);
                    if (string.IsNullOrEmpty(password))
                    {
                        conn.SetPassword(password);
                    }
                    conn.Open();//打开数据库
                }
                else
                {
                    MessageBox.Show("数据库路径名称不能为空！", "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper", "数据库创建出错，数据库名称为空");
                    return false;
                }
                if (!string.IsNullOrEmpty(password))
                {
                    conn.ChangePassword(password);     
                }
                comm = new SQLiteCommand();
                comm.Connection = conn;
                //conn.Close();//关闭数据库
                Log4Helper.WriteLog("sqliteHelper", "数据库创建成功");
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("打开数据库出错！"+Environment.NewLine+ex.Message, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.CreateDB", ex.Message);
                return false;   
            }
            
        }

        public override bool CloseDB()
        {
            try
            {
                if (comm!=null)
                {
                    comm.Cancel();
                }
                comm = null;
                if (dataReader!=null&&(!dataReader.IsClosed))
                {
                    dataReader.Close();
                }
                dataReader = null;
                if (conn!=null)
                {
                    conn.Close();
                }
                conn = null;
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("关闭数据库出错！" + Environment.NewLine + ex.Message, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.CloseDB", ex);
                return false;
            }
        }
        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <returns>成功删除返回true，否则返回false</returns>
        public override bool DeleteDB()
        {
            try
            {
                if (!string.IsNullOrEmpty(dbName))
                {
                    if (System.IO.File.Exists(dbName))
                    {
                        System.IO.File.Delete(dbName);
                        return true;
                    }
                    else
                    {
                        MessageBox.Show("没有找到相应的数据库，请检查！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        Log4Helper.WriteLog("sqliteHelper", "数据库删除出错，没有找到数据库文件");
                        return false;
                    }
                }
                else
                {
                    MessageBox.Show("数据库路径名称不能为空！", "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper", "数据库删除出错，数据库名称为空");
                    return false;
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("删除数据库出错！"+Environment.NewLine+ex.Message, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.DeleteDB", ex.Message);
                return false;
            }
        }
        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="colNames">表中列名称</param>
        /// <param name="colTypes">表中列对应的数据类型</param>
        /// <returns>成功创建返回true，否则返回false</returns>
        public override bool CreateTable(string TableName, string[] colNames, string[] colTypes)
        {
            try
            {
                if (conn==null)
                {
                    MessageBox.Show("没有找到相应的数据库，请先创建数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper.CreateTable", "创建表出错，没有找到数据库文件");
                    return false;
                }
                if (conn.State!=System.Data.ConnectionState.Open)
                {
                    OpenDB();
                }
                
                string text = string.Concat(new string[]
                    {
                        "CREATE TABLE IF NOT EXISTS ",
                        TableName,
                        "( ",
                        colNames[0],
                        " ",
                        colTypes[0]
                    });
                for (int i = 1; i < colNames.Length; i++)
                {
                    string text2 = text;
                    text = string.Concat(new string[]
                    {
                    text2,
                    ", ",
                    colNames[i],
                    " ",
                    colTypes[i]
                    });
                }
                text += "  ) ";
                comm = conn.CreateCommand();//不添加此句容易出错
                comm.CommandText = text;
                comm.ExecuteNonQuery();
                Log4Helper.WriteLog("sqliteHelper.CreateTable", "创建表:" + TableName + " 成功");
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("创建数据表出错！"+Environment.NewLine+ex.Message, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.DeleteDB", ex.Message);
                return false;
            }            
        }

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <returns>成功删除返回true，否则返回false</returns>
        public override bool DeleteTable(string TableName)
        {
            try
            {
                if (conn == null)
                {
                    MessageBox.Show("没有找到相应的数据库，请先选择数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper.DeleteTable", "删除表出错，没有找到数据库文件");
                    return false;
                }
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    OpenDB();
                }
                comm = conn.CreateCommand();//不添加此句容易出错
                comm.CommandText = "DROP TABLE IF EXISTS "+TableName;
                comm.ExecuteNonQuery();
                Log4Helper.WriteLog("sqliteHelper.DeleteTable", "删除表:"+TableName+" 成功");
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("创建数据表出错！" + Environment.NewLine + ex.Message, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.DeleteDB", ex.Message);
                return false;
            }
        }

        public override bool QueryFullTable(string TableName,ref SQLiteDataReader sqliteReader)
        {
           
            try
            {
                if (comm == null || conn == null)
                {
                    Log4Helper.WriteLog("sqliteHelper.QueryFullTable", "请先打开数据库！");
                    OpenDB();
                    //return false;
                }
                comm = conn.CreateCommand();//不添加此句容易出错
                string queryString = "SELECT * FROM " + TableName;
                comm.CommandText = queryString;
                //comm.ExecuteReader();
                sqliteReader= comm.ExecuteReader();
                return true;
            }
            catch (System.Exception ex)
            {
                Log4Helper.WriteLog("sqliteHelper.QueryFullTable", ex);
                return false;
            }
        }

        public override bool ExecuteQuery(string queryString, ref SQLiteDataReader sqliteReader)
        {
            try
            {
                if (comm == null || conn == null)
                {
                    Log4Helper.WriteLog("sqliteHelper.QueryFullTable", "请先打开数据库！");
                    return false;
                }
                comm = conn.CreateCommand();//不添加此句容易出错
                comm.CommandText = queryString;
                sqliteReader = comm.ExecuteReader();
                return true;
            }
            catch (System.Exception ex)
            {
                Log4Helper.WriteLog("sqliteHelper.ExecuteQuery", ex);
                return false;
            }            
        }

        public override bool InsertInto(string tableName, string[] context,ref SQLiteDataReader sqliteReader)
        {
            try
            {
                QueryFullTable(tableName,ref sqliteReader);
                int fieldCount = sqliteReader.FieldCount;
                if (context.Length!=fieldCount)
                {
                    MessageBox.Show("要插入的内容与数据库列数不对应！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper.InsertRow", "插入数据库出错");
                    return false;
                }
                //INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] 列可以不写，但值要与列对应 
                //VALUES(value1, value2, value3,...valueN);
                //INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
                //INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
                //VALUES(1, 'Paul', 32, 'California', 20000.00);
                // string  text="INSERT INTO "+tableName+" VALUES ('"+context[0]+"'";
                string text = string.Concat(new string[]
            {
                "INSERT INTO ",
                tableName,
                " VALUES ('",
                context[0],
                "'"
            });
                for (int i = 1; i < context.Length; i++)
                {
                    text = text + ", '" + context[i] + "'";
                }
                text += " )";
                ExecuteQuery(text,ref sqliteReader);
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("插入到数据库出错！"+Environment.NewLine+ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.InsertRow", ex);
                return false;
            }
        }

        public override bool ReadTable(string tableName, string[] items, string[] colNames, 
            string[] operations,string[] colOperas, string[] colValues,ref SQLiteDataReader sqliteReader)
        {
            try
            {
                string text;
                //SQLiteDataReader temp = null;
                //SELECT ID, NAME, SALARY FROM COMPANY; //只获取指定的字段内容（items）,items可以为空
                //SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
                // colNames长度、colValues长度、operateions长度一样
                if (colNames.Length!=operations.Length||colNames.Length!=colValues.Length||(colValues.Length-1)!=colOperas.Length)
                {
                    return false;
                }
                if (items==null||items.Length<1)
                {
                    text = "SELECT * ";
                }
                else
                {
                    text = "SELECT " + items[0];
                    for (int i = 1; i < items.Length; i++)
                    {
                        text = text + ", " + items[i];
                    }
                }
                //string text = "SELECT " + items[0];
                //for (int i = 1; i < items.Length; i++)
                //{
                //    text = text + ", " + items[i];
                //}
                string text2 = text;
                text = string.Concat(new string[]
                {
                text2,
                " FROM ",
                tableName,
                " WHERE ",
                colNames[0],
                " ",
                operations[0],
                " ",
                "'"+colValues[0]+"'"
                });
                for (int j = 1; j < colNames.Length; j++)
                {
                    string text3 = text;
                    text = string.Concat(new string[]
                    {
                    text3,
                    " ",
                    colOperas[j-1],
                    " ",
                    colNames[j],
                    " ",
                    operations[j],
                    " '",
                    colValues[j],
                    "' "
                    });
                }
                ExecuteQuery(text,ref sqliteReader);
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("查询数据库出错！" + Environment.NewLine + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.ReadInfo", ex);
                return false;
            }
        }

        public override bool ReadTable(string tableName, string colName, string operation,  string colValue,ref SQLiteDataReader sqliteReader)
        {
            try
            {
                //SQLiteDataReader temp = null;
                //SELECT ID, NAME, SALARY FROM COMPANY; //只获取指定的字段内容（items）,items可以为空
                //SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
                // colNames长度、colValues长度、operateions长度一样
                string text= "SELECT * FROM "+tableName+" WHERE "+colName+" "+ operation+" '"+colValue+"'";

                ExecuteQuery(text, ref sqliteReader);
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("查询数据库出错！" + Environment.NewLine + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.ReadInfo", ex);
                return false;
            }
        }

        public override bool DeleteInfo(string tableName, string[] colNames, string[] operations, string[] colOperas, string[] colValues)
        {
            try
            {
                SQLiteDataReader temp = null;
                if (colNames.Length != colValues.Length || operations.Length != colNames.Length || (operations.Length-1) != colOperas.Length)
                {

                    MessageBox.Show("删除数据库出错！数组长度不符", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper.DeleteInfo", "删除数据库出错！数组长度不符");
                    return false;
                }
                //DELETE FROM COMPANY WHERE ID = 7;
                string text = string.Concat(new string[]
                {
                "DELETE FROM ",
                tableName,
                " WHERE ",
                colNames[0],
                operations[0],
                "'",
                colValues[0],
                "'"
                });
                for (int i = 1; i < colValues.Length; i++)
                {
                    string text2 = text;
                    text = string.Concat(new string[]
                    {
                    text2,
                    " ",
                    colOperas[i-1],
                    " ",
                    colNames[i],
                    operations[i],
                    "'",
                    colValues[i],
                    "'"
                    });
                }

                ExecuteQuery(text,ref temp);
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("删除数据库出错！" + Environment.NewLine + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.ReadInfo", ex);
                return false;
            }
        }
        public override bool DeleteInfo(string tableName, string colName, string operation,string colValue)
        {
            try
            {
                SQLiteDataReader temp = null;
                //DELETE FROM COMPANY WHERE ID = 7;
                // string text="DELETE FROM "+tableName+" WHERE "+colName+" "+operation+" "+colValue;
                string text = string.Concat(new string[]
                {
                "DELETE FROM ",
                tableName,
                " WHERE ",
                colName,
                operation,
                "'",
                colValue,
                "'"
                });
                ExecuteQuery(text, ref temp);
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("删除数据库出错！" + Environment.NewLine + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.ReadInfo", ex);
                return false;
            }
        }

        public override bool UpdateInfo(string tableName, string[] colNames, string[] colValues, string key, string keyValue,string operation)
        {
            try
            {
                SQLiteDataReader temp = null;
                if (colNames.Length != colValues.Length)
                {
                    MessageBox.Show("删除数据库出错！数组长度不符", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Log4Helper.WriteLog("sqliteHelper.DeleteInfo", "删除数据库出错！数组长度不符");
                    return false;
                }
                string text = string.Concat(new string[]
                {
                "UPDATE ",
                tableName,
                " SET ",
                colNames[0],
                "='",
                colValues[0],
                "'"
                });
                for (int i = 1; i < colValues.Length; i++)
                {
                    string text2 = text;
                    text = string.Concat(new string[]
                    {
                    text2,
                    ", ",
                    colNames[i],
                    "='",
                    colValues[i],
                    "'"
                    });
                }
                string text3 = text;
                text = string.Concat(new string[]
                {
                text3,
                " WHERE ",
                key,
                operation,
                "'",
                keyValue,
                "'"
                });
                ExecuteQuery(text, ref temp);
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("更新数据库出错！" + Environment.NewLine + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Log4Helper.WriteLog("sqliteHelper.UpdateInfo", ex);
                return false;
            }
        }

    }
}
